import {
  mysqlTable,
  varchar,
  text,
  timestamp,
  json,
  int,
  boolean,
} from 'drizzle-orm/mysql-core';
import { relations } from 'drizzle-orm';

// 角色表
export const characters = mysqlTable('characters', {
  id: varchar('id', { length: 36 }).primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  background: text('background').notNull(), // 故事背景
  personality: json('personality').$type<string[]>().notNull(), // 性格特点数组
  speakingStyle: text('speaking_style').notNull(), // 说话风格
  knowledgeBase: text('knowledge_base').notNull(), // 知识领域
  avatar: varchar('avatar', { length: 255 }), // 头像URL
  systemPrompt: text('system_prompt').notNull(), // AI系统提示词
  isActive: boolean('is_active').default(true), // 是否激活
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdateNow(),
});

// 对话会话表
export const conversations = mysqlTable('conversations', {
  id: varchar('id', { length: 36 }).primaryKey(),
  characterId: varchar('character_id', { length: 36 }).notNull(),
  title: varchar('title', { length: 200 }), // 对话标题
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdateNow(),
});

// 消息表
export const messages = mysqlTable('messages', {
  id: varchar('id', { length: 36 }).primaryKey(),
  conversationId: varchar('conversation_id', { length: 36 }).notNull(),
  role: varchar('role', { length: 20 }).notNull(), // 'user' | 'assistant'
  content: text('content').notNull(),
  metadata: json('metadata').$type<Record<string, any>>(), // 额外元数据
  createdAt: timestamp('created_at').defaultNow(),
});

// 用户反馈表（可选）
export const feedbacks = mysqlTable('feedbacks', {
  id: varchar('id', { length: 36 }).primaryKey(),
  messageId: varchar('message_id', { length: 36 }).notNull(),
  rating: int('rating'), // 1-5星评分
  comment: text('comment'), // 反馈评论
  createdAt: timestamp('created_at').defaultNow(),
});

// 定义表关系
export const charactersRelations = relations(characters, ({ many }) => ({
  conversations: many(conversations),
}));

export const conversationsRelations = relations(conversations, ({ one, many }) => ({
  character: one(characters, {
    fields: [conversations.characterId],
    references: [characters.id],
  }),
  messages: many(messages),
}));

export const messagesRelations = relations(messages, ({ one, many }) => ({
  conversation: one(conversations, {
    fields: [messages.conversationId],
    references: [conversations.id],
  }),
  feedbacks: many(feedbacks),
}));

export const feedbacksRelations = relations(feedbacks, ({ one }) => ({
  message: one(messages, {
    fields: [feedbacks.messageId],
    references: [messages.id],
  }),
}));

// 导出类型
export type Character = typeof characters.$inferSelect;
export type NewCharacter = typeof characters.$inferInsert;
export type Conversation = typeof conversations.$inferSelect;
export type NewConversation = typeof conversations.$inferInsert;
export type Message = typeof messages.$inferSelect;
export type NewMessage = typeof messages.$inferInsert;
export type Feedback = typeof feedbacks.$inferSelect;
export type NewFeedback = typeof feedbacks.$inferInsert;
